package com.huaxia.dao.performance;

import com.huaxia.pojo.performance.SaleDeptPerformance;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/04/26 14:36
 */

public interface SaleDeptPerformanceMapper {

   /* @Select("select ss.saledeptsname,nvl(a.dayAdvance,0)saleDeptdayAdvance,nvl(b.monthAdvance,0)saleDeptmonthAdvance,(nvl(c.dayInSure,0)-nvl(d.dayYt,0))saleDeptdayInSure,(nvl(e.monthInSure,0)-nvl(f.monthYt,0))saleDeptmonthInSure from SD_SALEDEPT ss left join(\n" +
            "select SALEDEPTCODE, SUM(PRESTADPREM)dayAdvance from FACT_YX_PREPREM_LIST_day where RECEIVE_DATE=TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD') and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N' GROUP BY SALEDEPTCODE\n" +
            ")a on ss.SALEDEPTCODE=a.SALEDEPTCODE left join(\n" +
            "select SALEDEPTCODE, SUM(PRESTADPREM)monthAdvance from FACT_YX_PREPREM_LIST_day where TO_CHAR(RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N' GROUP BY SALEDEPTCODE\n" +
            ")b on ss.SALEDEPTCODE=b.SALEDEPTCODE left join(\n" +
            "select SALEDEPTCODE, SUM(WRITTENSTADPREM)dayInSure from FACT_YX_PREPREM_LIST_day where ISSUE_DATE=TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD')   AND RNFLAG='N' GROUP BY SALEDEPTCODE\n" +
            ")c on ss.SALEDEPTCODE=c.SALEDEPTCODE left join(\n" +
            "select SALEDEPTCODE, SUM(WRITTENSTADPREM)dayYt from FACT_YX_PREPREM_LIST_day where YT_DATE=TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD')  AND RNFLAG='N' GROUP BY SALEDEPTCODE\n" +
            ")d on ss.SALEDEPTCODE=d.SALEDEPTCODE left join(\n" +
            "select SALEDEPTCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') AND RNFLAG='N' GROUP BY SALEDEPTCODE\n" +
            ")e on ss.SALEDEPTCODE=e.SALEDEPTCODE left join(\n" +
            "select SALEDEPTCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' GROUP BY SALEDEPTCODE\n" +
            ")f on ss.SALEDEPTCODE=f.SALEDEPTCODE order by saleDeptmonthInSure desc")
    List<SaleDeptPerformance> getSaleDeptPerformance();*/


    @Select("select sb.saledeptsname,nvl(a.dayAdvance,0)saledeptDayAdvance,nvl(b.monthAdvance,0)saledeptMonthAdvance,(nvl(c.dayInSure,0)-nvl(d.dayYt,0))saledeptDayInSure,\n" +
            "            (nvl(e.monthInSure,0)-nvl(f.monthYt,0))saledeptMonthInSure from SD_SALEDEPT sb left join(\n" +
            "            select saledeptCODE, SUM(PRESTADPREM)dayAdvance from FACT_YX_PREPREM_LIST_day where TO_CHAR(RECEIVE_DATE,'YYYY/MM/DD')=\n" +
            "    TO_CHAR(SYSDATE,'YYYY/MM/DD') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                                    )a on sb.saledeptcode=a.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(PRESTADPREM)monthAdvance from FACT_YX_PREPREM_LIST_day where TO_CHAR(RECEIVE_DATE,'YYYY/MM')=\n" +
            "    TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N'\n" +
            "    GROUP BY saledeptCODE \n" +
            "                   )b on sb.saledeptcode=b.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(WRITTENSTADPREM)dayInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM/DD')=\n" +
            "    TO_CHAR(SYSDATE,'YYYY/MM/DD') AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                                    )c on sb.saledeptcode=c.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(WRITTENSTADPREM)dayYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM/DD')\n" +
            "            = TO_CHAR(SYSDATE,'YYYY/MM/DD') AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                                    )d on sb.saledeptcode=d.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')\n" +
            "            =TO_CHAR(SYSDATE,'YYYY/MM') AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                  )e on sb.saledeptcode=e.saledeptcode left join(\n" +
            "            select saledeptCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')\n" +
            "            =TO_CHAR(SYSDATE,'YYYY/MM') AND RNFLAG='N'  GROUP BY saledeptCODE\n" +
            "                                )f on sb.saledeptcode=f.saledeptcode")
    List<SaleDeptPerformance> getSaleDeptPerformanceThisMon();

    @Select("select sb.saledeptsname,nvl(a.dayAdvance,0)saledeptDayAdvance,nvl(b.monthAdvance,0)saledeptMonthAdvance,(nvl(c.dayInSure,0)-nvl(d.dayYt,0))saledeptDayInSure,\n" +
            "        (nvl(e.monthInSure,0)-nvl(f.monthYt,0))saledeptMonthInSure from SD_SALEDEPT sb left join(\n" +
            "                                        select saledeptCODE, SUM(PRESTADPREM)dayAdvance from FACT_YX_PREPREM_LIST_day where  ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')= \n" +
            "        to_char(SYSDATE,'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N') or (TO_CHAR(RECEIVE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM'))) and TO_CHAR(scan_DATE,'YYYY/MM/dd')=TO_CHAR(SYSDATE,'YYYY/MM/DD') and (APPLY_STATUS_CODE NOT IN\n" +
            "        ('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                       )a on sb.saledeptcode=a.saledeptcode left join(\n" +
            "                                        select saledeptCODE, SUM(PRESTADPREM)monthAdvance from FACT_YX_PREPREM_LIST_day where ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')=\n" +
            "        to_char(SYSDATE,'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N') or (TO_CHAR(RECEIVE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM'))) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N' \n" +
            "        GROUP BY saledeptCODE )b on sb.saledeptcode=b.saledeptcode left join(\n" +
            "                                        select saledeptCODE, SUM(WRITTENSTADPREM)dayInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')= \n" +
            "        to_char(add_months(trunc(sysdate),1),'YYYY/MM') and TO_CHAR(scan_DATE,'YYYY/MM/dd')=TO_CHAR(SYSDATE,'YYYY/MM/DD')  AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                        )c on sb.saledeptcode=c.saledeptcode left join(\n" +
            "                                        select saledeptCODE, SUM(WRITTENSTADPREM)dayYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(APPLY_YT_DATE,'YYYY/MM/DD')\n" +
            "        =TO_CHAR(SYSDATE,'YYYY/MM/DD') and  TO_CHAR(ISSUE_DATE,'YYYY/MM')= to_char(add_months(trunc(sysdate),1),'YYYY/MM')  AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                        )d on sb.saledeptcode=d.saledeptcode left join(\n" +
            "                                        select saledeptCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')\n" +
            "        =to_char(add_months(trunc(sysdate),1),'YYYY/MM') AND RNFLAG='N' GROUP BY saledeptCODE)e on sb.saledeptcode=e.saledeptcode left join(\n" +
            "                                        select saledeptCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')\n" +
            "        =to_char(add_months(trunc(sysdate),1),'YYYY/MM') and TO_CHAR(ISSUE_DATE,'YYYY/MM')= to_char(add_months(trunc(sysdate),1),'YYYY/MM') AND RNFLAG='N' \n" +
            "        GROUP BY saledeptCODE)f on sb.saledeptcode=f.saledeptcode")
    List<SaleDeptPerformance> getSaleDeptPerformanceNextMon();

    @Select("select sb.saledeptsname,nvl(a.dayAdvance,0)saledeptDayAdvance,nvl(b.monthAdvance,0)saledeptMonthAdvance,(nvl(c.dayInSure,0)-nvl(d.dayYt,0))saledeptDayInSure,\n" +
            "                   (nvl(e.monthInSure,0)-nvl(f.monthYt,0))saledeptMonthInSure from SD_SALEDEPT sb left join(\n" +
            "                                  select saledeptCODE, SUM(PRESTADPREM)dayAdvance from FACT_YX_PREPREM_LIST_day where TO_CHAR(RECEIVE_DATE,'YYYY/MM')= \n" +
            "                   TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) and TO_CHAR(scan_DATE,'YYYY/MM/dd')=TO_CHAR(SYSDATE,'YYYY/MM/DD') and (APPLY_STATUS_CODE NOT IN\n" +
            "                   ('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                                    )a on sb.saledeptcode=a.saledeptcode left join(\n" +
            "                                  select saledeptCODE, SUM(PRESTADPREM)monthAdvance from FACT_YX_PREPREM_LIST_day where TO_CHAR(RECEIVE_DATE,'YYYY/MM')=\n" +
            "                   TO_CHAR(SYSDATE,'YYYY/MM') and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N' \n" +
            "                   GROUP BY saledeptCODE \n" +
            "                   )b on sb.saledeptcode=b.saledeptcode left join(\n" +
            "                                 select saledeptCODE, SUM(WRITTENSTADPREM)dayInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')= \n" +
            "                   TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(scan_DATE,'YYYY/MM/dd')=TO_CHAR(SYSDATE,'YYYY/MM/DD')  AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                                   )c on sb.saledeptcode=c.saledeptcode left join(\n" +
            "                                 select saledeptCODE, SUM(WRITTENSTADPREM)dayYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(APPLY_YT_DATE,'YYYY/MM/DD')\n" +
            "                   =TO_CHAR(SYSDATE,'YYYY/MM/DD') and  TO_CHAR(ISSUE_DATE,'YYYY/MM')= TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                                                   )d on sb.saledeptcode=d.saledeptcode left join(\n" +
            "                                 select saledeptCODE, SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')\n" +
            "                   =to_char(SYSDATE,'YYYY/MM') AND RNFLAG='N' GROUP BY saledeptCODE\n" +
            "                  )e on sb.saledeptcode=e.saledeptcode left join(\n" +
            "                                select saledeptCODE, SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')\n" +
            "                   =to_char(SYSDATE,'YYYY/MM') and TO_CHAR(ISSUE_DATE,'YYYY/MM')= to_char(add_months(trunc(sysdate),1),'YYYY/MM') AND RNFLAG='N' \n" +
            "                   GROUP BY saledeptCODE\n" +
            "                )f on sb.saledeptcode=f.saledeptcode")
    List<SaleDeptPerformance> getSaleDeptPerformanceThisMon1st();

}
